/*
 Navicat Premium Data Transfer

 Source Server         : hunter
 Source Server Type    : MySQL
 Source Server Version : 80403 (8.4.3)
 Source Host           : localhost:3306
 Source Schema         : helin_health

 Target Server Type    : MySQL
 Target Server Version : 80403 (8.4.3)
 File Encoding         : 65001

 Date: 14/05/2025 02:54:09
*/

-- 如果存在数据库则先删除
DROP DATABASE IF EXISTS helin_health;

-- 创建数据库
CREATE DATABASE helin_health DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE helin_health;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '管理员ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `email` varchar(100) DEFAULT NULL COMMENT '电子邮箱',
  `role` tinyint DEFAULT '2' COMMENT '角色：1-超级管理员 2-普通管理员',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `status` tinyint DEFAULT '1' COMMENT '账号状态：0-禁用 1-启用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `is_deleted` tinyint DEFAULT '0' COMMENT '逻辑删除标记：0-未删除 1-已删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='管理员表';

-- ----------------------------
-- Records of admin
-- ----------------------------
BEGIN;
INSERT INTO `admin` (`id`, `username`, `password`, `nickname`, `avatar`, `phone`, `email`, `role`, `last_login_time`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'admin', '$2a$10$hBfv9No25j9r9uCgLpBydeZ6ZTLcL1GEnevOqtiRb7utuwEnpp80a', '超级管理员', NULL, NULL, NULL, 1, NULL, 1, '2025-05-13 10:48:10', '2025-05-13 10:48:10', 0);
INSERT INTO `admin` (`id`, `username`, `password`, `nickname`, `avatar`, `phone`, `email`, `role`, `last_login_time`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'test1', '$2a$10$R.HriIVkC0Vh1G1zPQNWH.u5VGPD1wbZPEXT0DPTng9c8V0D0QOCe', '超级管理员', NULL, '13800138000', 'admin@helinhealth.com', 2, '2025-05-13 10:57:15', 1, '2025-05-13 10:54:13', '2025-05-13 10:54:13', 0);
INSERT INTO `admin` (`id`, `username`, `password`, `nickname`, `avatar`, `phone`, `email`, `role`, `last_login_time`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 'test2', '$2a$10$LYMj..P7DKFmP8cIZkhNp.WpsHkS7ujmdXdqXgbg4lsOGU2OlBouW', '超级管理员', NULL, '13800138000', 'admin@helinhealth.com', 2, '2025-05-13 10:58:03', 1, '2025-05-13 10:57:26', '2025-05-13 10:57:26', 0);
COMMIT;

-- ----------------------------
-- Table structure for ai_qa
-- ----------------------------
DROP TABLE IF EXISTS `ai_qa`;
CREATE TABLE `ai_qa` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `question` varchar(255) DEFAULT NULL COMMENT '问题',
  `answer` text COMMENT '答案',
  `hit_count` int DEFAULT '0' COMMENT '命中次数',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_question` (`question`(20)) COMMENT '问题前缀索引'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='AI问答知识库';

-- ----------------------------
-- Records of ai_qa
-- ----------------------------
BEGIN;
INSERT INTO `ai_qa` (`id`, `question`, `answer`, `hit_count`, `create_time`, `update_time`, `is_deleted`) VALUES (1, '头痛怎么办', '建议测量血压，若持续请就医', 5, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `ai_qa` (`id`, `question`, `answer`, `hit_count`, `create_time`, `update_time`, `is_deleted`) VALUES (2, '忘记吃药怎么办', '如果距离下次服药时间超过2小时可补服，否则等到下次服药时间', 3, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `ai_qa` (`id`, `question`, `answer`, `hit_count`, `create_time`, `update_time`, `is_deleted`) VALUES (3, '高血压如何预防', '控制饮食、低盐饮食、适当运动、保持良好心态、按时服药', 8, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `ai_qa` (`id`, `question`, `answer`, `hit_count`, `create_time`, `update_time`, `is_deleted`) VALUES (4, '血糖高怎么办', '控制碳水化合物摄入，增加运动，按医嘱服用降糖药，定期检测血糖', 6, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `ai_qa` (`id`, `question`, `answer`, `hit_count`, `create_time`, `update_time`, `is_deleted`) VALUES (5, '老人跌倒如何处理', '保持冷静，检查伤势，如无法移动应立即呼叫救助，有明显外伤应及时就医', 4, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for children
-- ----------------------------
DROP TABLE IF EXISTS `children`;
CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '子女信息ID',
  `user_id` varchar(32) NOT NULL COMMENT '关联用户openid',
  `real_name` varchar(20) DEFAULT NULL COMMENT '真实姓名',
  `gender` tinyint DEFAULT NULL COMMENT '性别(0女，1男)',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) COMMENT '用户ID索引',
  CONSTRAINT `fk_children_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='子女信息表';

-- ----------------------------
-- Records of children
-- ----------------------------
BEGIN;
INSERT INTO `children` (`id`, `user_id`, `real_name`, `gender`, `phone`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'children_001', '张小明', 1, '13900139001', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `children` (`id`, `user_id`, `real_name`, `gender`, `phone`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'children_002', '李小红', 0, '13900139002', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `children` (`id`, `user_id`, `real_name`, `gender`, `phone`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 'children_003', '王小刚', 1, '13900139003', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `children` (`id`, `user_id`, `real_name`, `gender`, `phone`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 'children_004', '赵小花', 0, '13900139004', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for elderly
-- ----------------------------
DROP TABLE IF EXISTS `elderly`;
CREATE TABLE `elderly` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '老人信息ID',
  `user_id` varchar(32) NOT NULL COMMENT '关联用户openid',
  `real_name` varchar(20) DEFAULT NULL COMMENT '真实姓名',
  `gender` tinyint DEFAULT NULL COMMENT '性别(0女，1男)',
  `age` int DEFAULT NULL COMMENT '年龄',
  `address` varchar(255) DEFAULT NULL COMMENT '居住地址',
  `medical_history` text COMMENT '病史记录',
  `emergency_contact` varchar(20) DEFAULT NULL COMMENT '紧急联系电话',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) COMMENT '用户ID索引',
  CONSTRAINT `fk_elderly_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='老人信息表';

-- ----------------------------
-- Records of elderly
-- ----------------------------
BEGIN;
INSERT INTO `elderly` (`id`, `user_id`, `real_name`, `gender`, `age`, `address`, `medical_history`, `emergency_contact`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'elderly_001', '张大海', 1, 68, '北京市海淀区XX小区1号楼302', '高血压,糖尿病', '13900139001', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `elderly` (`id`, `user_id`, `real_name`, `gender`, `age`, `address`, `medical_history`, `emergency_contact`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'elderly_002', '李秀英', 0, 65, '北京市朝阳区XX花园2号楼502', '关节炎', '13900139002', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `elderly` (`id`, `user_id`, `real_name`, `gender`, `age`, `address`, `medical_history`, `emergency_contact`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 'elderly_003', '王建国', 1, 72, '北京市西城区XX胡同15号', '冠心病,高血压', '13900139003', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for emergency_call
-- ----------------------------
DROP TABLE IF EXISTS `emergency_call`;
CREATE TABLE `emergency_call` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用户openid',
  `location` varchar(100) DEFAULT NULL COMMENT '经纬度(如116.404,39.915)',
  `status` tinyint DEFAULT '0' COMMENT '状态(0未处理，1已处理)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`,`status`) COMMENT '用户和状态联合索引',
  CONSTRAINT `fk_emergency_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='紧急呼叫记录表';

-- ----------------------------
-- Records of emergency_call
-- ----------------------------
BEGIN;
INSERT INTO `emergency_call` (`id`, `user_id`, `location`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'elderly_001', '116.404,39.915', 1, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `emergency_call` (`id`, `user_id`, `location`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'elderly_003', '116.400,39.905', 0, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for emergency_notification
-- ----------------------------
DROP TABLE IF EXISTS `emergency_notification`;
CREATE TABLE `emergency_notification` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '通知ID',
  `emergency_id` int NOT NULL COMMENT '关联紧急呼叫ID',
  `children_id` int NOT NULL COMMENT '子女ID',
  `status` tinyint DEFAULT '0' COMMENT '通知状态(0未读,1已读,2已响应)',
  `response_time` datetime DEFAULT NULL COMMENT '响应时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_emergency_id` (`emergency_id`) COMMENT '紧急呼叫ID索引',
  KEY `idx_children_id` (`children_id`) COMMENT '子女ID索引',
  CONSTRAINT `fk_notification_children` FOREIGN KEY (`children_id`) REFERENCES `children` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notification_emergency` FOREIGN KEY (`emergency_id`) REFERENCES `emergency_call` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='紧急呼叫通知记录表';

-- ----------------------------
-- Records of emergency_notification
-- ----------------------------
BEGIN;
INSERT INTO `emergency_notification` (`id`, `emergency_id`, `children_id`, `status`, `response_time`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 1, 1, 2, '2025-04-27 09:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `emergency_notification` (`id`, `emergency_id`, `children_id`, `status`, `response_time`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 1, 4, 1, NULL, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `emergency_notification` (`id`, `emergency_id`, `children_id`, `status`, `response_time`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 2, 3, 0, NULL, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for export_task
-- ----------------------------
DROP TABLE IF EXISTS `export_task`;
CREATE TABLE `export_task` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `user_id` varchar(32) NOT NULL COMMENT '用户ID',
  `type` tinyint NOT NULL COMMENT '导出类型(1-健康记录, 2-用药记录, 3-紧急呼叫记录)',
  `start_date` datetime DEFAULT NULL COMMENT '开始日期',
  `end_date` datetime DEFAULT NULL COMMENT '结束日期',
  `file_path` varchar(255) DEFAULT NULL COMMENT '导出文件路径',
  `format` tinyint DEFAULT '1' COMMENT '导出格式(1-Excel, 2-PDF, 3-CSV)',
  `status` tinyint DEFAULT '0' COMMENT '任务状态(0-处理中, 1-已完成, 2-失败)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记(0未删除，1已删除)',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) COMMENT '用户ID索引',
  KEY `idx_status` (`status`) COMMENT '状态索引'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据导出任务表';

-- ----------------------------
-- Records of export_task
-- ----------------------------
BEGIN;
INSERT INTO `export_task` (`id`, `user_id`, `type`, `start_date`, `end_date`, `file_path`, `format`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'elderly_001', 1, '2025-01-01 00:00:00', '2025-04-30 00:00:00', '/exports/health_1_1747114862148.xlsx', 1, 1, '2025-05-13 13:40:57', '2025-05-13 13:41:02', 0);
INSERT INTO `export_task` (`id`, `user_id`, `type`, `start_date`, `end_date`, `file_path`, `format`, `status`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'elderly_001', 2, '2025-01-01 00:00:00', '2025-04-30 00:00:00', '/exports/medicine_2_1747115257979.xlsx', 1, 1, '2025-05-13 13:47:33', '2025-05-13 13:47:37', 0);
COMMIT;

-- ----------------------------
-- Table structure for family_relation
-- ----------------------------
DROP TABLE IF EXISTS `family_relation`;
CREATE TABLE `family_relation` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '关系ID',
  `elderly_id` int NOT NULL COMMENT '老人ID',
  `children_id` int NOT NULL COMMENT '子女ID',
  `relation_type` tinyint DEFAULT '1' COMMENT '关系类型(1子女,2孙子女,3其他)',
  `is_emergency_contact` tinyint(1) DEFAULT '0' COMMENT '是否为紧急联系人(0否,1是)',
  `notification_level` tinyint DEFAULT '1' COMMENT '通知级别(1全部通知,2仅紧急通知,3不通知)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_elderly_id` (`elderly_id`) COMMENT '老人ID索引',
  KEY `idx_children_id` (`children_id`) COMMENT '子女ID索引',
  CONSTRAINT `fk_relation_children` FOREIGN KEY (`children_id`) REFERENCES `children` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_relation_elderly` FOREIGN KEY (`elderly_id`) REFERENCES `elderly` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='亲属关系表';

-- ----------------------------
-- Records of family_relation
-- ----------------------------
BEGIN;
INSERT INTO `family_relation` (`id`, `elderly_id`, `children_id`, `relation_type`, `is_emergency_contact`, `notification_level`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 1, 1, 1, 1, 1, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `family_relation` (`id`, `elderly_id`, `children_id`, `relation_type`, `is_emergency_contact`, `notification_level`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 2, 2, 1, 1, 1, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `family_relation` (`id`, `elderly_id`, `children_id`, `relation_type`, `is_emergency_contact`, `notification_level`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 3, 3, 1, 1, 1, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `family_relation` (`id`, `elderly_id`, `children_id`, `relation_type`, `is_emergency_contact`, `notification_level`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 1, 4, 2, 0, 2, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `family_relation` (`id`, `elderly_id`, `children_id`, `relation_type`, `is_emergency_contact`, `notification_level`, `create_time`, `update_time`, `is_deleted`) VALUES (5, 2, 3, 3, 0, 2, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for health_record
-- ----------------------------
DROP TABLE IF EXISTS `health_record`;
CREATE TABLE `health_record` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用户openid',
  `type` tinyint DEFAULT NULL COMMENT '1血压 2血糖 3心率',
  `value` varchar(20) DEFAULT NULL COMMENT '记录值(如120/80)',
  `measure_time` datetime DEFAULT NULL COMMENT '测量时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_type` (`user_id`,`type`) COMMENT '用户和类型联合索引',
  CONSTRAINT `fk_health_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康记录表';

-- ----------------------------
-- Records of health_record
-- ----------------------------
BEGIN;
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'elderly_001', 1, '120/80', '2025-04-26 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'elderly_001', 2, '6.2', '2025-04-26 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 'elderly_002', 1, '135/85', '2025-04-26 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 'elderly_003', 1, '125/82', '2025-04-26 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (5, 'elderly_001', 3, '78', '2025-04-26 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (6, 'elderly_001', 1, '122/78', '2025-04-25 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (7, 'elderly_001', 2, '6.0', '2025-04-25 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (8, 'elderly_002', 1, '130/83', '2025-04-25 11:31:39', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (9, 'elderly_001', 1, '120/80', NULL, '2025-05-13 13:59:45', '2025-05-13 13:59:45', 0);
INSERT INTO `health_record` (`id`, `user_id`, `type`, `value`, `measure_time`, `create_time`, `update_time`, `is_deleted`) VALUES (10, 'elderly_001', 2, '5.6', NULL, '2025-05-13 14:00:04', '2025-05-13 14:00:04', 0);
COMMIT;

-- ----------------------------
-- Table structure for health_report
-- ----------------------------
DROP TABLE IF EXISTS `health_report`;
CREATE TABLE `health_report` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '报告ID',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用户openid',
  `content` text COMMENT '报告内容',
  `start_date` date DEFAULT NULL COMMENT '开始日期',
  `end_date` date DEFAULT NULL COMMENT '结束日期',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`,`start_date`) COMMENT '用户和日期联合索引',
  CONSTRAINT `fk_report_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康报告表';

-- ----------------------------
-- Records of health_report
-- ----------------------------
BEGIN;
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 'elderly_001', '本周血压平稳，血糖略有波动但在正常范围，建议继续保持良好作息', '2025-04-20', '2025-04-27', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 'elderly_002', '本周血压偏高，建议增加服药监测频率，减少盐分摄入', '2025-04-20', '2025-04-27', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 'elderly_003', '本周心率正常，血压稳定，建议适当增加户外活动', '2025-04-20', '2025-04-27', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 'elderly_001', '健康报告摘要\n报告时间范围: null 至 null\n\n血压监测情况:\n- 监测次数: 3次\n- 血压总体保持稳定\n\n血糖监测情况:\n- 监测次数: 3次\n- 血糖总体保持稳定\n\n建议: 请保持健康的生活方式，均衡饮食，适当运动，按时服药，定期复查。', NULL, NULL, '2025-05-13 14:00:44', '2025-05-13 14:00:44', 0);
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (5, 'elderly_001', '健康报告摘要\n报告时间范围: null 至 null\n\n血压监测情况:\n- 监测次数: 3次\n- 血压总体保持稳定\n\n血糖监测情况:\n- 监测次数: 3次\n- 血糖总体保持稳定\n\n建议: 请保持健康的生活方式，均衡饮食，适当运动，按时服药，定期复查。', NULL, NULL, '2025-05-13 14:01:19', '2025-05-13 14:01:19', 0);
INSERT INTO `health_report` (`id`, `user_id`, `content`, `start_date`, `end_date`, `create_time`, `update_time`, `is_deleted`) VALUES (6, 'elderly_001', '健康报告摘要\n报告时间范围: 2025-01-01 至 2025-12-01\n\n血压监测情况:\n- 监测次数: 3次\n- 血压总体保持稳定\n\n血糖监测情况:\n- 监测次数: 3次\n- 血糖总体保持稳定\n\n建议: 请保持健康的生活方式，均衡饮食，适当运动，按时服药，定期复查。', '2025-01-01', '2025-12-01', '2025-05-13 14:01:56', '2025-05-13 14:01:56', 0);
COMMIT;

-- ----------------------------
-- Table structure for health_share
-- ----------------------------
DROP TABLE IF EXISTS `health_share`;
CREATE TABLE `health_share` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '分享ID',
  `elderly_id` int NOT NULL COMMENT '老人ID',
  `children_id` int NOT NULL COMMENT '子女ID',
  `report_id` int DEFAULT NULL COMMENT '关联健康报告ID',
  `share_date` date DEFAULT NULL COMMENT '分享日期',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读(0未读,1已读)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_elderly_children` (`elderly_id`,`children_id`) COMMENT '老人和子女联合索引',
  KEY `idx_report_id` (`report_id`) COMMENT '报告ID索引',
  KEY `fk_share_children` (`children_id`),
  CONSTRAINT `fk_share_children` FOREIGN KEY (`children_id`) REFERENCES `children` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_share_elderly` FOREIGN KEY (`elderly_id`) REFERENCES `elderly` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_share_report` FOREIGN KEY (`report_id`) REFERENCES `health_report` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='健康日报分享表';

-- ----------------------------
-- Records of health_share
-- ----------------------------
BEGIN;
INSERT INTO `health_share` (`id`, `elderly_id`, `children_id`, `report_id`, `share_date`, `is_read`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 1, 1, 1, '2025-04-27', 1, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_share` (`id`, `elderly_id`, `children_id`, `report_id`, `share_date`, `is_read`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 2, 2, 2, '2025-04-27', 0, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_share` (`id`, `elderly_id`, `children_id`, `report_id`, `share_date`, `is_read`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 3, 3, 3, '2025-04-27', 0, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `health_share` (`id`, `elderly_id`, `children_id`, `report_id`, `share_date`, `is_read`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 1, 4, 1, '2025-04-27', 0, '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for medicine
-- ----------------------------
DROP TABLE IF EXISTS `medicine`;
CREATE TABLE `medicine` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '药品ID',
  `name` varchar(50) NOT NULL COMMENT '药品名称',
  `time` varchar(100) DEFAULT NULL COMMENT '服用时间(如08:00,12:00)',
  `user_id` varchar(32) DEFAULT NULL COMMENT '关联用户openid',
  `is_taken` tinyint(1) DEFAULT '0' COMMENT '是否已服用(0未服，1已服)',
  `stock_days` int DEFAULT '7' COMMENT '剩余天数',
  `dosage` varchar(50) DEFAULT NULL COMMENT '服用剂量(如：1片/次)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  `version` int DEFAULT '1' COMMENT '乐观锁版本号',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) COMMENT '用户ID索引',
  CONSTRAINT `fk_medicine_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='药品表';

-- ----------------------------
-- Records of medicine
-- ----------------------------
BEGIN;
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (1, '阿司匹林', '08:00,20:00', 'elderly_001', 0, 2, '1片/次', '2025-04-27 11:31:39', '2025-04-29 17:56:05', 0, 2);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (2, '降压药', '07:00,19:00', 'elderly_001', 0, 15, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (3, '维生素D', '12:00', 'elderly_002', 0, 30, '1粒/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (4, '降糖药', '08:00,12:00,18:00', 'elderly_001', 0, 20, '2片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (5, '钙片', '08:30', 'elderly_002', 0, 25, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (6, '心脏药', '09:00,21:00', 'elderly_003', 0, 15, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (10, '帅哥药', '11:00', 'elderly_001', 1, 40, '1片/次', '2025-04-27 12:20:28', '2025-04-29 15:32:57', 1, 1);
INSERT INTO `medicine` (`id`, `name`, `time`, `user_id`, `is_taken`, `stock_days`, `dosage`, `create_time`, `update_time`, `is_deleted`, `version`) VALUES (11, '大帅哥药', '11:00', 'elderly_001', 1, 40, '1片/次', '2025-04-29 15:38:04', '2025-04-29 15:38:15', 0, 1);
COMMIT;

-- ----------------------------
-- Table structure for medicine_log
-- ----------------------------
DROP TABLE IF EXISTS `medicine_log`;
CREATE TABLE `medicine_log` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `medicine_id` int DEFAULT NULL COMMENT '关联药品ID',
  `user_id` varchar(32) DEFAULT NULL COMMENT '用户openid',
  `taken_time` datetime DEFAULT NULL COMMENT '实际服用时间',
  `is_ontime` tinyint(1) DEFAULT NULL COMMENT '是否按时服用(0迟服，1准时)',
  `dosage` varchar(50) DEFAULT NULL COMMENT '实际服用剂量',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_medicine_id` (`medicine_id`) COMMENT '药品ID索引',
  KEY `idx_user_id` (`user_id`) COMMENT '用户ID索引',
  CONSTRAINT `fk_log_medicine` FOREIGN KEY (`medicine_id`) REFERENCES `medicine` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`openid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服药记录表';

-- ----------------------------
-- Records of medicine_log
-- ----------------------------
BEGIN;
INSERT INTO `medicine_log` (`id`, `medicine_id`, `user_id`, `taken_time`, `is_ontime`, `dosage`, `create_time`, `update_time`, `is_deleted`) VALUES (1, 1, 'elderly_001', '2025-04-26 11:31:39', 1, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `medicine_log` (`id`, `medicine_id`, `user_id`, `taken_time`, `is_ontime`, `dosage`, `create_time`, `update_time`, `is_deleted`) VALUES (2, 2, 'elderly_001', '2025-04-26 11:31:39', 1, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `medicine_log` (`id`, `medicine_id`, `user_id`, `taken_time`, `is_ontime`, `dosage`, `create_time`, `update_time`, `is_deleted`) VALUES (3, 3, 'elderly_002', '2025-04-26 11:31:39', 1, '1粒/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `medicine_log` (`id`, `medicine_id`, `user_id`, `taken_time`, `is_ontime`, `dosage`, `create_time`, `update_time`, `is_deleted`) VALUES (4, 1, 'elderly_001', '2025-04-25 11:31:39', 0, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
INSERT INTO `medicine_log` (`id`, `medicine_id`, `user_id`, `taken_time`, `is_ontime`, `dosage`, `create_time`, `update_time`, `is_deleted`) VALUES (5, 2, 'elderly_001', '2025-04-25 11:31:39', 1, '1片/次', '2025-04-27 11:31:39', '2025-04-27 11:31:39', 0);
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `openid` varchar(32) NOT NULL COMMENT '微信openid',
  `username` varchar(50) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `nickname` varchar(50) DEFAULT NULL COMMENT '微信昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `phone` varchar(20) DEFAULT NULL COMMENT '紧急联系电话',
  `role` tinyint DEFAULT '1' COMMENT '用户角色(1老人，2子女)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标记(0未删除，1已删除)',
  PRIMARY KEY (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('admin_001', 'admin', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '系统管理员', 'https://avatar.url/admin.jpg', NULL, 3, '2025-05-07 21:40:21', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('children_001', 'user_001', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '张小明', 'https://avatar.url/children1.jpg', '13900139001', 2, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('children_002', 'user_002', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '李小红', 'https://avatar.url/children2.jpg', '13900139002', 2, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('children_003', 'user_003', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '王小刚', 'https://avatar.url/children3.jpg', '13900139003', 2, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('children_004', 'user_004', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '赵小花', 'https://avatar.url/children4.jpg', '13900139004', 2, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('elderly_001', 'user_001', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '张爷爷', 'https://avatar.url/elderly1.jpg', '13800138001', 1, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('elderly_002', 'user_002', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '李奶奶', 'https://avatar.url/elderly2.jpg', '13800138002', 1, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
INSERT INTO `user` (`openid`, `username`, `password`, `nickname`, `avatar`, `phone`, `role`, `create_time`, `update_time`, `is_deleted`) VALUES ('elderly_003', 'user_003', '$2a$10$iFHdMQK8i5.KEOEe5mQpou9PPn0O6K1CyYLBQAJ52X3YYnDuuTdTm', '王大爷', 'https://avatar.url/elderly3.jpg', '13800138003', 1, '2025-04-27 11:31:39', '2025-05-07 21:40:21', 0);
COMMIT;

# 为expor_task表增加file_name字段
ALTER TABLE export_task ADD COLUMN file_name VARCHAR(255) COMMENT '显示文件名';

SET FOREIGN_KEY_CHECKS = 1;
